package demo1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lishunlin110
 * @version 1.0
 * @description: 学生类DAO
 * @date 2021/8/21 17:43
 */
public class StudentDAOImpl  {

    public int insert(Students students) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps=null;
        String sql ="insert into student values (?,?,?,?)";
        try {
            String id = students.getId();
            String name=students.getName();
            String dept_name=students.getDept_name();
            int tot_cred=students.getTot_cred();
            ps=connection.prepareStatement(sql);
            ps.setString(1,id);
            ps.setString(2,name);
            ps.setString(3,dept_name);
            ps.setInt(4,tot_cred);
            return ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(connection,ps,null);
        }
        return 0;
    }


    public int delete(String id) {
        Connection con=null;
        PreparedStatement ps=null;
        String sql="delete from student where ID=?";
        try {
            con = JDBCUtils.getConnection();
            ps=con.prepareStatement(sql);
            ps.setString(1,id);
            return ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return 0;
    }


    public int update(Students students) {

        Connection con=null;
        PreparedStatement ps=null;
        String sql="update student set name=?,dept_name=?,tot_cred=? where ID=?";
        try {
            String id = students.getId();
            String name=students.getName();
            String dept_name=students.getDept_name();
            int tot_cred=students.getTot_cred();
            con = JDBCUtils.getConnection();
            ps=con.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,dept_name);
            ps.setInt(3,tot_cred);
            ps.setString(4,id);
            return ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return 0;
    }


    public Students query(String id) {
        Connection con=null;
        PreparedStatement ps=null;
        String sql="select* from student  where ID=?";
        try {
            con = JDBCUtils.getConnection();
            ps=con.prepareStatement(sql);
            ps.setString(1,id);
            ResultSet rs = ps.executeQuery();
            Students st = new Students();
            if(rs.next()){
                st.setId(rs.getString("ID"));
                st.setId(rs.getString("name"));
                st.setId(rs.getString("dept_name"));
                st.setId(rs.getString("tot_cred"));
            }
            return st;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return null;
    }


    public List<Students>  queryAll() {

        Connection con=null;
        PreparedStatement ps=null;
        String sql="select* from student";
        List<Students> studentsList = new ArrayList<>();
        try {
            con = JDBCUtils.getConnection();
            ps=con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Students st = new Students();
                st.setId(rs.getString("ID"));
                st.setId(rs.getString("name"));
                st.setId(rs.getString("dept_name"));
                st.setId(rs.getString("tot_cred"));
                studentsList.add(st);
            }
            return studentsList;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeAll(con,ps,null);
        }
        return null;
    }
}
